﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Drawing.Charts;
using Hangfire;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql;
using Npoi.Mapper;
using NPOI.HPSF;
using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Asn1.Ocsp;
using Quartz.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using static housingsafety.server.Controllers.HouseMngController;
using static housingsafety.server.Controllers.SampleTaskController;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 大屏基本信息 展示
    /// </summary>
    [AllowAnonymous]
    public class HouseMngCheckController : BaseController<HouseMngCheckController>
    {
        public IConfiguration Configuration { get; }
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildings> _buildingsRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsLayerRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdateRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdate;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmPlaceCommercial> _placeCommercialRepository;
        private readonly IRepository<ZmPlaceResident> _placeResidentRepository;
        private readonly IOptions<StaticDataConf> _staticVtOptions;
        private readonly IRepository<ZmDepartment> _departmentRepository;
        private readonly IRepository<RiskCensusHouseYhpc> _riskHouseRepository;
        private readonly IRepository<RiskCensusYhpcUpldFile> _riskHouseFileRepository;
        private readonly IRepository<ZmPlaceResidentCheck> _placeResidentCheckRepository;
        private readonly IRepository<ZmPlaceCommercialCheck> _placeCommercialCheckRepository;
        private readonly IRepository<ZmYtPhoto> _zmYtPhotoRepository;
        private readonly IDbTransaction _transaction;

        private readonly Microsoft.AspNetCore.Http.Features.FormOptions _defaultFormOptions = new Microsoft.AspNetCore.Http.Features.FormOptions();
        private readonly string[] _permittedExtensions = { ".mp3", ".mp4", ".jpg", ".jpeg", ".png", ".geojson", ".doc", ".docx" };

        private static object _lock = new object();

        public HouseMngCheckController(IStorageProvider storageProvider
            , IRepository<ZmDepartment> departmentRepository
            , IRepository<ZmUser> userRepository
              , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
              , IRepository<ZmBuildings> buildingsRepository
            , IRepository<ZmBuildingsLayer> buildingsLayerRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmPlaceCommercial> placeCommercialRepository
            , IRepository<ZmPlaceResident> placeResidentRepository
            , IRepository<RiskCensusHouseYhpc> riskHouseRepository
            , IRepository<RiskCensusYhpcUpldFile> riskHouseFileRepository
            , IRepository<ZmPlaceResidentCheck> placeResidentCheckRepository
            , IRepository<ZmPlaceCommercialCheck> placeCommercialCheckRepository
            , IRepository<ZmYtPhoto> zmYtPhotoRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , IOptions<StaticDataConf> staticVtOptions
            , ILogger<HouseMngCheckController> logger,
            IConfiguration configuration
            , IRepository<ZmSystemLog> systemLogRepository, IRepository<ZmUpdateLog> zmupdateRepository) : base(logger, systemLogRepository)
        {
            _zmupdateRepository = zmupdateRepository;
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();
            _zmYtPhotoRepository = zmYtPhotoRepository;
            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _buildingsLayerRepository = buildingsLayerRepository;
            _fileAttachRepository = fileAttachRepository;
            _placeCommercialRepository = placeCommercialRepository;
            _placeResidentRepository = placeResidentRepository;
            _staticVtOptions = staticVtOptions;
            _departmentRepository = departmentRepository;
            _riskHouseRepository = riskHouseRepository;
            _riskHouseFileRepository = riskHouseFileRepository;
            _placeResidentCheckRepository = placeResidentCheckRepository;
            _placeCommercialCheckRepository = placeCommercialCheckRepository;
            Configuration = configuration;
            _villageFireSafetyRepository = villageFireSafetyRepository;
            _zmupdateRepository = zmupdateRepository;

        }

        List<string> stringList = new List<string> { "栋", "栋", "处", "处" };

        #region//人员疏散通道情况 
        /// <summary>
        /// 人员疏散通道情况--房屋总数
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/rysstdqk/fwzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> fwzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
				select xzq,fwbh,address,hzxm,village,town From zm_buildings where villagedm like @code and fwbh not in (select fwbh from zm_pc_base_cj where fwzt is not null and village like @code  ) order by fwbh limit @limit offset @offset 
				 ";

                string sqlcount = @" select count(1)  From zm_buildings where villagedm like @code and fwbh not in (select fwbh from zm_pc_base_cj where fwzt is not null and village like @code  ) ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/rysstdqk/fwzs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/fwzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }


        /// <summary>
        /// 人员疏散通道情况--正在施工改造
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/rysstdqk/zzsggz")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> zzsggz([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
				select xzq,fwbh,address,hzxm,village,town From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL    and sstdjsqk='正在施工改造') order by fwbh limit @limit offset @offset  
				 ";

                string sqlcount = @" select count(1) From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL    and sstdjsqk='正在施工改造')  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/rysstdqk/zzsggz", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/zzsggz", res);
                res.Msg = "系统异常";
                return res;
            }
        }


        /// <summary>
        /// 人员疏散通道情况--正在制定方案
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/rysstdqk/zzzdfa")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> zzzdfa([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
				select xzq,fwbh,address,hzxm,village,town From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL   and sstdjsqk='正在制定方案') order by fwbh limit @limit offset @offset  
				 ";

                string sqlcount = @" select count(1) From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL   and sstdjsqk='正在制定方案')  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/rysstdqk/zzzdfa", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/zzzdfa", res);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        /// 人员疏散通道情况--建筑室外或内天井增设疏散楼梯
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/rysstdqk/jjswhntjzssslt")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> jjswhntjzssslt([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
				select xzq,fwbh,address,hzxm,village,town From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL   and sstdjsqk like '%建筑室外或内天井增设疏散楼梯%') order by fwbh limit @limit offset @offset  
				 ";

                string sqlcount = @" select count(1) From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是' and t5.villagedm like @code  
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL   and sstdjsqk like '%建筑室外或内天井增设疏散楼梯%')  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/rysstdqk/jjswhntjzssslt", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/jjswhntjzssslt", res);
                res.Msg = "系统异常";
                return res;
            }
        }


        /// <summary>
        /// 人员疏散通道情况--建筑室内增设疏散楼梯
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/rysstdqk/jjsnzssslt")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> jjsnzssslt([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
				select xzq,fwbh,address,hzxm,village,town From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL   and sstdjsqk like '%建筑室内增设疏散楼梯%') order by fwbh limit @limit offset @offset  
				 ";

                string sqlcount = @" select count(1) From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL    and sstdjsqk like '%建筑室内增设疏散楼梯%')  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/rysstdqk/jjsnzssslt", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/jjsnzssslt", res);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        /// 人员疏散通道情况--利用相邻或相近建筑疏散
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/rysstdqk/lyxlhxjjzss")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> lyxlhxjjzss([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
				select xzq,fwbh,address,hzxm,village,town From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL    and sstdjsqk like '%利用相邻或相近建筑疏散%') order by fwbh limit @limit offset @offset  
				 ";

                string sqlcount = @" select count(1) From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL    and sstdjsqk like '%利用相邻或相近建筑疏散%')  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/rysstdqk/lyxlhxjjzss", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/lyxlhxjjzss", res);
                res.Msg = "系统异常";
                return res;
            }
        }



        /// <summary>
        /// 人员疏散通道情况--未达标
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/rysstdqk/wdbfw")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> wdbfw([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
								select xzq,fwbh,address,hzxm,village,town From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL    and  tdsfdb='否') limit @limit offset @offset  		 
				  ";

                string sqlcount = @" select count(1) From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL    and  tdsfdb='否' )  		 
				 ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/rysstdqk/wdbfw", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/wdbfw", res);
                res.Msg = "系统异常";
                return res;
            }
        }



        /// <summary>
        /// 人员疏散通道情况--达标
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/rysstdqk/ydbfw")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> ydbfw([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
								select xzq,fwbh,address,hzxm,village,town From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL    and  tdsfdb='是') limit @limit offset @offset  		 
				  ";

                string sqlcount = @" select count(1) From zm_buildings where fwbh in (SELECT 
            fwbh
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,sstdjsqk,t1.create_on, t1.xzq,tdsfdb, t1.xzqdm,t1.jzxyssltsl, t1.fwbh,fwzt 
            FROM  zm_pc_baselog t1 
            JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh 
            WHERE  sfyzc='是'  and t5.villagedm like @code
        ) AS zm_pc_base where row_num =1  AND fwzt IS NULL   and  tdsfdb='是' )  		 
				 ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/rysstdqk/ydbfw", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/ydbfw", res);
                res.Msg = "系统异常";
                return res;
            }
        }

        #endregion

        #region//自查覆盖率
        /// <summary>
        /// 自查覆盖率--房屋总数
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_31fgv2j/fwzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> jfwzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
				select xzq,fwbh,address,hzxm,village,town From zm_buildings where villagedm like @code and fwbh not in (select fwbh from zm_pc_base_cj where fwzt is not null and village like @code order by fwbh limit @limit offset @offset  )
				 ";

                string sqlcount = @" select count(1)  From zm_buildings where villagedm like @code and fwbh not in (select fwbh from zm_pc_base_cj where fwzt is not null and village like @code  ) ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/fwzs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/fwzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }



        /// <summary>
        /// 自查覆盖率--自查总数
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_31fgv2j/zczs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> zczs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" select xzq,fwbh,address,hzxm,village,town  from zm_buildings where villagedm like @code and fwbh in (select t1.fwbh from zm_pc_baselog t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null ) order by fwbh limit @limit offset @offset ";

                string sqlcount = @"  select count(1)  from zm_buildings where villagedm like @code and fwbh in (select t1.fwbh from zm_pc_baselog t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null   )  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit,@startime=param.startime,@endtime=param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/zczs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/fwzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        /// 自查覆盖率--隐患总数（栋）
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_31fgv2j/yhzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> yhzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }
                string sql = @" select xzq,fwbh,address,hzxm,village,town  from zm_buildings where villagedm like @code and fwbh in (select distinct t1.fwbh from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null  ) order by fwbh limit @limit offset @offset ";

                string sqlcount = @"  select count(1)  from zm_buildings where villagedm like @code and fwbh in (select distinct t1.fwbh from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null  )  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/zczs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/yhzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }


        /// <summary>
        /// 自查覆盖率--整改总数（栋）
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_31fgv2j/zgzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> zgzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }
                string sql = @" select xzq,fwbh,address,hzxm,village,town  from zm_buildings where villagedm like @code and fwbh in (select distinct t1.fwbh from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null  and t1.zg_value = 1 ) order by fwbh limit @limit offset @offset ";

                string sqlcount = @"  select count(1)  from zm_buildings where villagedm like @code and fwbh in (select distinct t1.fwbh from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null  and t1.zg_value = 1 )  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/zczs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/yhzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }

        #endregion

        #region//检查覆盖率
        /// <summary>
        /// 检查覆盖率--房屋总数
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_31fgv2j/jcfwzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> jcfwzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 
				select xzq,fwbh,address,hzxm,village,town From zm_buildings where villagedm like @code and fwbh not in (select t1.fwbh from zm_pc_base_cj where fwzt is not null and village like @code order by fwbh limit @limit offset @offset  )
				 ";

                string sqlcount = @" select count(1)  From zm_buildings where villagedm like @code and fwbh not in (select t1.fwbh from zm_pc_base_cj where fwzt is not null and village like @code  ) ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/jcfwzs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/jcfwzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }



        /// <summary>
        /// 检查覆盖率--检查总数
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_31fgv2j/jczs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> jczs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" select xzq,fwbh,address,hzxm,village,town  from zm_buildings where villagedm like @code and fwbh in (select t1.fwbh from zm_pc_baselog_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null  ) order by fwbh limit @limit offset @offset ";

                string sqlcount = @"  select count(1)  from zm_buildings where villagedm like @code and fwbh in (select t1.fwbh from zm_pc_baselog_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null   )  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/zczs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/rysstdqk/fwzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        /// 自查覆盖率--隐患总数（栋）
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_31fgv2j/jcyhzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> jcyhzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }
                string sql = @" select xzq,fwbh,address,hzxm,village,town  from zm_buildings where villagedm like @code and fwbh in (select distinct t1.fwbh from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null  ) order by fwbh limit @limit offset @offset ";

                string sqlcount = @"  select count(1)  from zm_buildings where villagedm like @code and fwbh in (select distinct t1.fwbh from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null  )  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/zczs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/yhzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }


        /// <summary>
        /// 自查覆盖率--整改总数（栋）
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_31fgv2j/jczgzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> jczgzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }
                string sql = @" select xzq,fwbh,address,hzxm,village,town  from zm_buildings where villagedm like @code and fwbh in (select distinct t1.fwbh from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null  and t1.zg_value = 1 ) order by fwbh limit @limit offset @offset ";

                string sqlcount = @"  select count(1)  from zm_buildings where villagedm like @code and fwbh in (select distinct t1.fwbh from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh where t1.create_on between @startime  and @endtime and t2.fwzt is null and t1.zg_value = 1  )  ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/zczs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/dp_31fgv2j/yhzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }

        #endregion


        #region//自查隐患整改率
        /// <summary>
        /// 隐患整改率自查--隐患总数
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_33yhzgl2j/yhzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> yhzgyhzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }
                string sql = @" 		
						select case when zg_value ='1' then '已整改' when zg_value='0' then '未整改' end zg_value,check_nickname,to_char(check_time, 'YYYY-MM-DD HH24:MI:SS') check_time,t2.xzqdm,t2.xzq,t2.towndm,t2.town,t2.village,t2.villagedm,t2.fwbh ,t2.address,t2.hzxm,check_id,check_name               
         from zm_pc_yhlog_dx t1 join zm_buildings t2 on t1.fwbh=t2.fwbh  where t1.create_on between @startime and @endtime
						and t1.villagedm like @code
							and t1.fwbh not in (select distinct fwbh from zm_pc_base_cj where fwbh  is not null and villagedm like @code)
							 ";

                string sqlcount = @" select count(1) from zm_pc_yhlog_dx t1 join zm_buildings t2 on t1.fwbh=t2.fwbh  where t1.create_on between @startime and @endtime
						and t1.villagedm like @code
							and t1.fwbh not in (select distinct fwbh from zm_pc_base_cj where fwbh  is not null and villagedm like @code)
							 ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit,@startime=param.startime,@endtime=param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_33yhzgl2j/yhzs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/dp_33yhzgl2j/yhzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }


        /// <summary>
        /// 隐患整改率--整改总数
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_33yhzgl2j/zgzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> qhfxfwzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 		
						select case when zg_value ='1' then '已整改' when zg_value='0' then '未整改' end zg_value,check_nickname,to_char(check_time, 'YYYY-MM-DD HH24:MI:SS') check_time,t2.xzqdm,t2.xzq,t2.towndm,t2.town,t2.village,t2.villagedm,t2.fwbh ,t2.address,t2.hzxm,check_id,check_name               
         from zm_pc_yhlog_dx t1 join zm_buildings t2 on t1.fwbh=t2.fwbh  where t1.create_on between @startime and @endtime
						and t1.villagedm like @code and t1.zg_value ='1'
							and t1.fwbh not in (select distinct fwbh from zm_pc_base_cj where fwbh  is not null and villagedm like @code)
							 ";

                string sqlcount = @" select count(1) from zm_pc_yhlog_dx t1 join zm_buildings t2 on t1.fwbh=t2.fwbh  where t1.create_on between @startime and @endtime
						and t1.villagedm like @code and t1.zg_value ='1'
							and t1.fwbh not in (select distinct fwbh from zm_pc_base_cj where fwbh  is not null and villagedm like @code)
							 "; var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_33yhzgl2j/zgzs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/dp_33yhzgl2j/zgzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }
        #endregion



        #region//检查隐患整改率
        /// <summary>
        /// 隐患整改率自查--隐患总数
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_33yhzgl2j/jcyhzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> yhzg_jcyhzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }
                string sql = @" 		
						select case when zg_value ='1' then '已整改' when zg_value='0' then '未整改' end zg_value,check_nickname,to_char(check_time, 'YYYY-MM-DD HH24:MI:SS') check_time,t2.xzqdm,t2.xzq,t2.towndm,t2.town,t2.village,t2.villagedm,t2.fwbh ,t2.address,t2.hzxm,check_id,check_name               
         from zm_pc_yhlog_dx_cj t1 join zm_buildings t2 on t1.fwbh=t2.fwbh  where t1.create_on between @startime and @endtime
						and t1.villagedm like @code
							and t1.fwbh not in (select distinct fwbh from zm_pc_base_cj where fwbh  is not null and villagedm like @code)
							 ";

                string sqlcount = @" select count(1) from zm_pc_yhlog_dx_cj t1 join zm_buildings t2 on t1.fwbh=t2.fwbh  where t1.create_on between @startime and @endtime
						and t1.villagedm like @code
							and t1.fwbh not in (select distinct fwbh from zm_pc_base_cj where fwbh  is not null and villagedm like @code)
							 ";
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_33yhzgl2j/jcyhzs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/dp_33yhzgl2j/jcyhzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }


        /// <summary>
        /// 隐患整改率--整改总数
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("houses/dp_33yhzgl2j/jczgzs")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<Page<object>> yhzg_qhfxfwzs([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null)
                {
                    return new RespRet<Page<object>>(false, "请传递参数");
                }

                string sql = @" 		
						select case when zg_value ='1' then '已整改' when zg_value='0' then '未整改' end zg_value,check_nickname,to_char(check_time, 'YYYY-MM-DD HH24:MI:SS') check_time,t2.xzqdm,t2.xzq,t2.towndm,t2.town,t2.village,t2.villagedm,t2.fwbh ,t2.address,t2.hzxm,check_id,check_name               
         from zm_pc_yhlog_dx t1 join zm_buildings t2 on t1.fwbh=t2.fwbh  where t1.create_on between @startime and @endtime
						and t1.villagedm like @code and t1.zg_value ='1'
							and t1.fwbh not in (select distinct fwbh from zm_pc_base_cj where fwbh  is not null and villagedm like @code)
							 ";

                string sqlcount = @" select count(1) from zm_pc_yhlog_dx t1 join zm_buildings t2 on t1.fwbh=t2.fwbh  where t1.create_on between @startime and @endtime
						and t1.villagedm like @code and t1.zg_value ='1'
							and t1.fwbh not in (select distinct fwbh from zm_pc_base_cj where fwbh  is not null and villagedm like @code)
							 "; var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(sqlcount, new { @code = "%" + param.code + "%", @limit = param.Limit, @offset = (param.PageIndex - 1) * param.Limit, @startime = param.startime, @endtime = param.endtime }).FirstOrDefault();
                var res = new RespRet<Page<object>>(true, "Ok", new Page<object>(data
                    , param.PageIndex + 1, param.Limit, count));
                WriteSystemLog(usernNumber, "houses/dp_33yhzgl2j/jczgzs", res);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLog(usernNumber, "houses/dp_33yhzgl2j/jczgzs", res);
                res.Msg = "系统异常";
                return res;
            }
        }
        #endregion

        /// <summary>
        /// 导出隐患房屋
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("houses/export/jcyhHourseExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult jcyhHourseExport([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string type, [FromQuery] string check_id)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else  //乡镇
                    {
                        strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                    }
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                }
                string sql = "";
                if (type == "sy")
                {
                    sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,'未整改' zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where zg_value='0' and fwbh not in(select fwbh from zm_pc_base_sqjz where yhly='4' and fwzt is not null)  order by m1.towndm, m1.villagedm) t where 1=1 and check_id='"+ check_id + "' " +
                       " " + strWhere;
                }
                else if (type == "zg")
                {
                    sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,'已整改' zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where zg_value='1' and fwbh not in(select fwbh from zm_pc_base_sqjz where yhly='4' and fwzt is not null)  order by m1.towndm, m1.villagedm) t where 1=1 and check_id='"+ check_id + "' " +
                       " " + strWhere;
                }
                else if (type == "yh")
                {
                    sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,case when zg_value=1 then '已整改' else '未整改' end zgzt  from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null and fwbh not in(select fwbh from zm_pc_base_sqjz where yhly='4' and fwzt is not null) ) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where 1=1  order by m1.towndm, m1.villagedm) t where 1=1  and check_id='"+ check_id + "'  " +
                       " " + strWhere;
                }
                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("房屋台账");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("隐患状态");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            //var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "towndm")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zgzt")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }

                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "隐患房屋" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }

}